package cn.zclong.reply.dao;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.itcast.commons.CommonUtils;
import cn.itcast.jdbc.TxQueryRunner;
import cn.zclong.article.domain.Article;
import cn.zclong.article.domain.PageBean;
import cn.zclong.reply.domain.Reply;

public class ReplyDao {
	private QueryRunner qr = new TxQueryRunner();

	/*
	 * 查询所有帖子的回复
	 */
	public PageBean<Reply> findAll(int pc, int ps, String id) {
		try {
			/*
			 * 1，得到pageBean对象的pb 2,设置pb的pc当前页码 和 ps每页记录数 3，得到tr总记录数,设置给pb
			 * 4,得到beanList,设置给pb 5，返回pb
			 */
			PageBean<Reply> pb = new PageBean<Reply>();
			pb.setPc(pc);
			pb.setPs(ps);
			/*
			 * 得到tr
			 */
			/*
			 * 得到评论者的id和姓名 distinct:去掉重复行
			 */
			// String replyname = "select username from t_user where uid=(select
			// distinct userid from reply where aid=?)";
			//
			// // 获取单行单列的结果ScalarHandler
			// String name = (String) qr.query(replyname, new ScalarHandler(),
			// id);
			// System.out.println(name);
			// pb.setUsername(name);

			/*
			 * 1，给出一个SQL语句前半部
			 */
			StringBuilder cntsql = new StringBuilder("select count(*) from reply");

			StringBuilder wheresql = new StringBuilder(" where aid=? ORDER BY reply.time desc");

			/*
			 * 2，执行SQL语句
			 */
			Number num = (Number) qr.query(cntsql.append(wheresql).toString(), new ScalarHandler(), id);
			int tr = num.intValue();
			pb.setTr(tr);

			if (tr > 0) {
				/*
				 * 得到评论时间
				 */
				String replytime = "select time from reply where aid=?";
				// 获取单行单列的结果ScalarHandler
				long time = (long) qr.query(replytime, new ScalarHandler(), id);
				if (time > 0) {
					// 得到时间

					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
					String time1 = sdf.format(new Date(time));
					pb.setTime(time1);
				}
			}
			/*
			 * 得到beanList
			 */
			StringBuilder sql = new StringBuilder("select * from reply");
			// 查询beanList这一步需要给出limit字句
			StringBuilder limitSql = new StringBuilder(" limit ?,?");

			String sql1 = "SELECT time,message,ip,username,integral,userid FROM (SELECT * FROM reply WHERE aid = ?) "
					+ "AS reply2 LEFT JOIN (SELECT uid AS usersId,username,integral FROM t_user) "
					+ "AS userstable ON reply2.userid=userstable.usersId where reply2.audit=? ORDER BY reply2.time DESC LIMIT ?,?";

			// List<Reply> beanList =
			// qr.query(sql.append(wheresql).append(limitSql).toString(),
			// new BeanListHandler<Reply>(Reply.class), id, (pc - 1) * ps, ps);
			List<Reply> beanList = qr.query(sql1, new BeanListHandler<Reply>(Reply.class), id, 1, (pc - 1) * ps, ps);

			pb.setBeanList(beanList);
			return pb;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/*
	 * 我要评论，插入评论
	 *  aid:要评论的帖子的id 
	 *  userId：评论者的id 
	 *  message：评论的内容 
	 *  ip：评论者的ip
	 */
	public void addReply(String aid, String userId, String message, String ip) {
		try {
			String id = CommonUtils.uuid();
			long time = new Date().getTime();
			String sql = "insert  into `reply`(`rid`,`time`,`message`,`aid`,`ip`,`userid`) values (?,?,?,?,?,?);";

			qr.update(sql, id, time, message, aid, ip, userId);

			// 先找出原来的积分量，评论加10分
			sql = "select integral from t_user where uid=?";
			long integral = Long.parseLong("" + qr.query(sql, new ScalarHandler(), userId));
			integral = integral + 10;
			sql = "update t_user set integral =? where uid=?";
			qr.update(sql, integral, userId);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	
	/*
	 * 查询所有帖子,含有回复信息
	 */
	
	public PageBean<Article> adminFindById(int pc, int ps) {
		
			try {
				
				/*
				 * 1，得到pageBean对象的pb 2,设置pb的pc当前页码 和 ps每页记录数 3，得到tr总记录数,设置给pb
				 * 4,得到beanList,设置给pb 5，返回pb
				 */
				PageBean<Article> pb = new PageBean<Article>();
				pb.setPc(pc);
				pb.setPs(ps);
				/*
				 * 得到tr
				 */
				String sql = "select count(*) from article where article.audit=?";
				Number num = (Number) qr.query(sql, new ScalarHandler(), 1);
				int tr = num.intValue();
				pb.setTr(tr);
				/*
				 * 得到beanList
				 */
				sql = "select article.*,username,t_user.uid from article "
						+ "left join (select uid,username from t_user) as t_user"
						+ " ON article.uid=t_user.uid "
						+ "where article.audit=?"
						+ " ORDER BY article.time desc limit ?,?";// 降序
				List<Article> beanList = qr.query(sql, new BeanListHandler<Article>(Article.class), 1, (pc - 1) * ps, ps);
				pb.setBeanList(beanList);
				return pb;
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
	
	}

	/*
	 * 管理员审核帖子的回复
	 */
	public PageBean<Reply> adminFindReplyById(int pc, int ps, String id) {
		try {
			/*
			 * 1，得到pageBean对象的pb 2,设置pb的pc当前页码 和 ps每页记录数 3，得到tr总记录数,设置给pb
			 * 4,得到beanList,设置给pb 5，返回pb
			 */
			PageBean<Reply> pb = new PageBean<Reply>();
			pb.setPc(pc);
			pb.setPs(ps);
			
			System.out.println(id);
			/*
			 * 1，给出一个SQL语句前半部
			 */
			StringBuilder cntsql = new StringBuilder("select count(*) from reply");

			StringBuilder wheresql = new StringBuilder(" where aid=? ORDER BY reply.time desc");

			/*
			 * 2，执行SQL语句
			 */
			/*
			 * 得到tr
			 */
			Number num = (Number) qr.query(cntsql.append(wheresql).toString(), new ScalarHandler(), id);
			int tr = num.intValue();
			pb.setTr(tr);

			if (tr > 0) {
				/*
				 * 得到评论时间
				 */
				String replytime = "select time from reply where aid=?";
				// 获取单行单列的结果ScalarHandler
				long time = (long) qr.query(replytime, new ScalarHandler(), id);
				if (time > 0) {
					// 得到时间

					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
					String time1 = sdf.format(new Date(time));
					pb.setTime(time1);
				}
			}
			/*
			 * 得到beanList
			 */
			StringBuilder sql = new StringBuilder("select * from reply");
			// 查询beanList这一步需要给出limit字句
			StringBuilder limitSql = new StringBuilder(" limit ?,?");

			String sql1 = "SELECT rid,aid,time,message,ip,username,integral,userid,audit FROM (SELECT * FROM reply WHERE aid = ?) AS reply2 LEFT JOIN (SELECT uid AS usersId,username,integral FROM t_user) AS userstable ON reply2.userid=userstable.usersId ORDER BY reply2.time DESC LIMIT ?,?";

			List<Reply> beanList = qr.query(sql1, new BeanListHandler<Reply>(Reply.class), id, (pc - 1) * ps, ps);

			pb.setBeanList(beanList);
			return pb;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	/*
	 * 将审核状态改变为1，通过审核 
	 */
	public void adminUpdateById(String userId, String rid) {
		try {
			String sql = "update reply set audit=? where rid=? ";
			qr.update(sql, 1, rid);
			// 先找出原来的积分量，评论加10分
			sql = "select integral from t_user where uid=?";
			long integral = Long.parseLong("" + qr.query(sql, new ScalarHandler(), userId));
			integral = integral + 10;
			sql = "update t_user set integral =? where uid=?";
			qr.update(sql, integral, userId);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
	}

	/*
	 * 删除该帖子的某一个回复
	 */
	public void adminDeleteById(String rid) {
		try {
			String sql = "delete from reply where rid=?";
			qr.update(sql, rid);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
	}

	/*
	 * 删除该帖子的所有回复
	 */
	public void adminDeleteAll(String aid) {
		try {
			System.out.println(aid);
			String sql = "delete from reply where aid=?";
			int  i = qr.update(sql, aid);
			System.out.println(i);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
	}

}
